﻿CREATE PROCEDURE [dbo].[weeklyHoursByActivity]
(
  @userId INT,
  @endDate DATETIME
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @startDate DATETIME

SET @startDate = DateAdd(day,-7,@endDate)

SELECT
  c.Name AS Company,
  CAST(at.name AS VARCHAR(30)) AS Activity,
  CASE WHEN SUM(tl.duration) > 0 
    THEN SUM(tl.duration) / 60
    ELSE 0 
  END AS HoursLogged
  FROM
    timeLog tl
    INNER JOIN activityType at ON at.activityTypeId = tl.activityTypeId
    INNER JOIN role r ON r.roleId = at.roleId
    INNER JOIN company c ON c.companyId = r.companyId
    INNER JOIN companyUser cu ON cu.companyId = r.companyId
  WHERE 
    tl.createDate BETWEEN @startDate AND @endDate
    AND
    cu.userId = @userId
  GROUP BY
   c.name,
   at.name
  ORDER BY
   c.name,
   at.name,
   HoursLogged
   
END
RETURN